USE MASTER
GO
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'StudentManagementIntelligent')
DROP DATABASE StudentManagementIntelligent
GO
create database StudentManagementIntelligent
go
use StudentManagementIntelligent
go
create table Student(
	StudentID nvarchar(100) primary key,
	Name nvarchar(100) not null,
	Gender nvarchar(100) not null check(Gender = 'male' or Gender = 'female'),
	DOB smalldatetime not null,
	[Address] nvarchar(100) not null,
	Email nvarchar(100) not null,
	Phone nvarchar(100) not null,
	[Status] nvarchar(100) default 'waiting' check([Status] = 'finished' or [Status]='learning' or [Status] = 'waiting')
)
go
create table Teacher(
	TeacherID nvarchar(100) primary key,	
	[Password] nvarchar(100)not null,
	Name nvarchar(100) not null,
	Gender nvarchar(100) not null check(Gender = 'male' or Gender = 'female'),
	DOB smalldatetime not null,
	[Address] nvarchar(100) not null,
	Email nvarchar(100) not null,
	Phone nvarchar(100) not null,
	[Description] nvarchar(100)
)
go
create table Manager(
	ManagerID nvarchar(100) primary key,
	[Password] nvarchar(100)not null,
	Name nvarchar(100) not null,
	Gender nvarchar(100) not null check(Gender = 'male' or Gender = 'female'),
	DOB smalldatetime not null,
	[Address] nvarchar(100) not null,
	Email nvarchar(100) not null,
	Phone nvarchar(100) not null,
	[Description] nvarchar(100)
)

go
create table Class(
	ClassID nvarchar(100) primary key,
	TeacherID nvarchar(100) foreign key references  Teacher(TeacherID),
	ClassName nvarchar(100) not null,
	TimeStart smalldatetime ,
	TimeEnd smalldatetime,
	TimeSlot nvarchar(100) default 'none' check(TimeSlot='g' or TimeSlot='i' or TimeSlot='l'),
	Room nvarchar(100)
)
go

create table ClassDetail(
	ClassID nvarchar(100) foreign key references Class(ClassID),
	StudentID nvarchar(100) foreign key references Student(StudentID),
	[Status] nvarchar(100) not null check([Status] = 'learning' or [Status] = 'finished'),
	constraint pk_ClassDetalID primary key (ClassID,StudentID)
)
go

create table ClassHistory(
	ClassID nvarchar(100) foreign key references Class(ClassID),
	TeacherID nvarchar(100) foreign key references Teacher(TeacherID),
	TeachTimeStart smalldatetime,
	TeachTimeEnd smalldatetime,
	constraint pk_ClassHistoryID primary key (ClassID,TeacherID,TeachTimeStart)
)
go

create table [Subject](
	SubjectID nvarchar(100) primary key,
	SubjectName nvarchar(100) not null,
	Sem nvarchar(100) not null,
	[Description] nvarchar(100),
	TotalSession int not null
)
go
-- change---

create table Attendance(
	AttendanceID nvarchar(100) primary key,
	--AttendanceDetailID nvarchar(100) foreign key references AttendanceDetail(AttendanceDetailID),
	ClassID nvarchar(100) foreign key references Class(ClassID),
	TimeAttendance smalldatetime default getdate()
	
)
go
create table AttendanceDetail(
	AttendanceDetailID nvarchar(100) primary key,
	AttendanceID nvarchar(100) foreign key references Attendance(AttendanceID),
	StudentID nvarchar(100) foreign key references Student(StudentID),
	SubjectID nvarchar(100) foreign key references [Subject](SubjectID),	
	[Status] nvarchar(100) not null check ([Status] = 'present' or [Status] = 'absent'),
	SessionCount int not null
	
)
go
----
create table TeacherAvatar(
	TeacherID nvarchar(100) foreign key references Teacher(TeacherID),	
	Avatar image
)
GO
create table StudentAvatar
(
	StudentID nvarchar(100) foreign key references Student(StudentID),
	Avatar image
)
GO

--create table Sem
--(
--	SubjectID nvarchar(100) primary key references [Subject](SubjectID),
--	Semester nvarchar(100) not null
--)

